--    Author    : MAYANTING
--    Name      : ADM.ANA_SUST_DLMG_RATE_DEP_RATE.HQL
--    Functions :
--    Purpose   : Daily saving the variation data from stg
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2018-05-24  MAYANTING        1.CREATE THE PROCEDURE
--

INSERT OVERWRITE TABLE ADM.ANA_SUST_DLMG_RATE_DEP_RATE PARTITION(DATA_DATE = '#V_DATA_DATE#')
   SELECT
           SUBSTR('#V_DATA_DATE#',1,6)                AS DATA_MONTH
          ,T.ORG_CODE_1                               AS ORG_CODE_1
          ,T.ORG_DSCR_1                               AS ORG_DSCR_1
          ,T.ORG_CODE_2                               AS ORG_CODE_2
          ,T.ORG_DSCR_2                               AS ORG_DSCR_2
          ,T.ORG_CODE_3                               AS ORG_CODE_3
          ,T.ORG_DSCR_3                               AS ORG_DSCR_3
          ,T.AREA_CODE_1                              AS AREA_CODE_1
          ,T.AREA_DSCR_1                              AS AREA_DSCR_1
          ,T.AREA_CODE_2                              AS AREA_CODE_2
          ,T.AREA_DSCR_2                              AS AREA_DSCR_2
          ,T.AREA_CODE_3                              AS AREA_CODE_3
          ,T.AREA_DSCR_3                              AS AREA_DSCR_3
          ,T.CUSTOMER_TYPE                            AS CUSTOMER_TYPE     --  客户类型 单位客户=0；个人客户=1
          ,T.PRODUCT_CATEGORY                         AS PRODUCT_CATEGORY  --  存款产品类别  展示一级
          ,T.DEP_PERIOD                               AS DEP_PERIOD        --  存款期限 1:1个月（含）以内  2:1个月至3个月（含） 3:3个月至6个月（含）  4:6个月至1年（含）  5:1至2年（含）  6:2至3年（含）  7:3至5年（含）8:5至10年（含）9:10年以上 10:未定期限
          ,T.CCY                                      AS CCY               -- 币种
          -- 处理单位为亿元
          ,SUM(T.DEP_BAL         )/100000000          AS DEP_BAL
          ,SUM(T.DEP_BAL_LM      )/100000000          AS DEP_BAL_LM
          ,SUM(T.DEP_BAL_LY      )/100000000          AS DEP_BAL_LY
          ,SUM(T.WSUM_BAL        )/100000000          AS WSUM_BAL
          ,SUM(T.WSUM_BAL_LM     )/100000000          AS WSUM_BAL_LM
          ,SUM(T.WSUM_BAL_LY     )/100000000          AS WSUM_BAL_LY
          ,SUM(T.RATE_SUM_BAL    )/100000000          AS RATE_SUM_BAL
          ,SUM(T.RATE_SUM_BAL_LM )/100000000          AS RATE_SUM_BAL_LM
          ,SUM(T.RATE_SUM_BAL_LY )/100000000          AS RATE_SUM_BAL_LY
          ,SUM(T.BAL_COUNT       )                    AS BAL_COUNT
          ,SUM(T.BAL_COUNT_LM    )                    AS BAL_COUNT_LM
          ,SUM(T.BAL_COUNT_LY    )                    AS BAL_COUNT_LY
          ,SUM(T.DEP_AMT         )/100000000          AS DEP_AMT
          ,SUM(T.DEP_AMT_LM      )/100000000          AS DEP_AMT_LM
          ,SUM(T.DEP_AMT_LY      )/100000000          AS DEP_AMT_LY
          ,SUM(T.WSUM_AMT        )/100000000          AS WSUM_AMT
          ,SUM(T.WSUM_AMT_LM     )/100000000          AS WSUM_AMT_LM
          ,SUM(T.WSUM_AMT_LY     )/100000000          AS WSUM_AMT_LY
          ,SUM(T.RATE_SUM_AMT    )/100000000          AS RATE_SUM_AMT
          ,SUM(T.RATE_SUM_AMT_LM )/100000000          AS RATE_SUM_AMT_LM
          ,SUM(T.RATE_SUM_AMT_LY )/100000000          AS RATE_SUM_AMT_LY
          ,SUM(T.AMT_COUNT       )                    AS AMT_COUNT
          ,SUM(T.AMT_COUNT_LM    )                    AS AMT_COUNT_LM
          ,SUM(T.AMT_COUNT_LY    )                    AS AMT_COUNT_LY
    FROM
        (       SELECT   ORG.ORG_CODE_1                AS ORG_CODE_1
                        ,ORG.ORG_DSCR_1                AS ORG_DSCR_1
                        ,ORG.ORG_CODE_2                AS ORG_CODE_2
                        ,ORG.ORG_DSCR_2                AS ORG_DSCR_2
                        ,ORG.ORG_CODE_3                AS ORG_CODE_3
                        ,ORG.ORG_DSCR_3                AS ORG_DSCR_3
                        ,AREA.AREA_CODE_1              AS AREA_CODE_1
                        ,AREA.AREA_DSCR_1              AS AREA_DSCR_1
                        ,AREA.AREA_CODE_3              AS AREA_CODE_2
                        ,AREA.AREA_DSCR_3              AS AREA_DSCR_2
                        ,AREA.AREA_CODE_4              AS AREA_CODE_3
                        ,AREA.AREA_DSCR_4              AS AREA_DSCR_3
                        ,DEP.CLIENT_TYPE               AS CUSTOMER_TYPE        --  客户类型 单位客户=0；个人客户=1
                        ,PRO.DEPT_PRODUCT_ID_1         AS PRODUCT_CATEGORY     --  存款产品类别  展示一级
                        ,DEP.TERM_CODE                 AS DEP_PERIOD           --  存款期限 1:1个月（含）以内  2:1个月至3个月（含） 3:3个月至6个月（含）  4:6个月至1年（含）  5:1至2年（含）  6:2至3年（含）  7:3至5年（含）8:5至10年（含）9:10年以上 10:未定期限
                        ,DEP.CCY                       AS CCY                  --  币种
                        ,CASE WHEN DEP.DATA_DATE='#V_DATA_DATE#'       THEN COALESCE(DEP.ACTUAL_BAL,0) ELSE 0      END       AS DEP_BAL             -- 当月存款余额
                        ,CASE WHEN DEP.DATA_DATE='#V_PREV_MONTH_DATE#' THEN COALESCE(DEP.ACTUAL_BAL,0) ELSE 0      END       AS DEP_BAL_LM          -- 上月存款余额
                        ,CASE WHEN DEP.DATA_DATE='#V_PREV_YEAR_DATE#'  THEN COALESCE(DEP.ACTUAL_BAL,0) ELSE 0      END       AS DEP_BAL_LY          -- 去年同期存款余额
                        ,CASE WHEN DEP.DATA_DATE='#V_DATA_DATE#'       THEN COALESCE(DEP.WSUM_BAL,0)   ELSE 0      END       AS WSUM_BAL            -- 当月余额加权金额
                        ,CASE WHEN DEP.DATA_DATE='#V_PREV_MONTH_DATE#' THEN COALESCE(DEP.WSUM_BAL,0)   ELSE 0      END       AS WSUM_BAL_LM         -- 上月余额加权金额
                        ,CASE WHEN DEP.DATA_DATE='#V_PREV_YEAR_DATE#'  THEN COALESCE(DEP.WSUM_BAL,0)   ELSE 0      END       AS WSUM_BAL_LY         -- 去年同期余额加权金额
                        ,CASE WHEN DEP.DATA_DATE='#V_DATA_DATE#'       THEN COALESCE(DEP.RATE,0)       ELSE 0      END       AS RATE_SUM_BAL        -- 当月存款余额利率合计
                        ,CASE WHEN DEP.DATA_DATE='#V_PREV_MONTH_DATE#' THEN COALESCE(DEP.RATE,0)       ELSE 0      END       AS RATE_SUM_BAL_LM     -- 上月存款余额利率合计
                        ,CASE WHEN DEP.DATA_DATE='#V_PREV_YEAR_DATE#'  THEN COALESCE(DEP.RATE,0)       ELSE 0      END       AS RATE_SUM_BAL_LY     -- 去年同期余额利率合计
                        ,CASE WHEN DEP.DATA_DATE='#V_DATA_DATE#'       THEN 1                                      END       AS BAL_COUNT           -- 当月存款余额笔数
                        ,CASE WHEN DEP.DATA_DATE='#V_PREV_MONTH_DATE#' THEN 1                                      END       AS BAL_COUNT_LM        -- 上月存款余额笔数
                        ,CASE WHEN DEP.DATA_DATE='#V_PREV_YEAR_DATE#'  THEN 1                                      END       AS BAL_COUNT_LY        -- 去年同期存款余额笔数
                        ,CASE WHEN DEP.DATA_DATE='#V_DATA_DATE#'       THEN COALESCE(DEP.ACTUAL_AMOUNT,0) ELSE 0   END       AS DEP_AMT             -- 当月存款发生额
                        ,CASE WHEN DEP.DATA_DATE='#V_PREV_MONTH_DATE#' THEN COALESCE(DEP.ACTUAL_AMOUNT,0) ELSE 0   END       AS DEP_AMT_LM          -- 上月存款发生额
                        ,CASE WHEN DEP.DATA_DATE='#V_PREV_YEAR_DATE#'  THEN COALESCE(DEP.ACTUAL_AMOUNT,0) ELSE 0   END       AS DEP_AMT_LY          -- 去年同期存款发生额
                        ,CASE WHEN DEP.DATA_DATE='#V_DATA_DATE#'       THEN COALESCE(DEP.WSUM_AMOUNT,0)   ELSE 0   END       AS WSUM_AMT            -- 当月发生额加权金额
                        ,CASE WHEN DEP.DATA_DATE='#V_PREV_MONTH_DATE#' THEN COALESCE(DEP.WSUM_AMOUNT,0)   ELSE 0   END       AS WSUM_AMT_LM         -- 上月发生额加权金额
                        ,CASE WHEN DEP.DATA_DATE='#V_PREV_YEAR_DATE#'  THEN COALESCE(DEP.WSUM_AMOUNT,0)   ELSE 0   END       AS WSUM_AMT_LY         -- 去年同期发生额加权金额
                        ,CASE WHEN DEP.DATA_DATE='#V_DATA_DATE#'       THEN COALESCE(DEP.RATE,0)          ELSE 0   END       AS RATE_SUM_AMT        -- 当月存款发生额利率合计
                        ,CASE WHEN DEP.DATA_DATE='#V_PREV_MONTH_DATE#' THEN COALESCE(DEP.RATE,0)          ELSE 0   END       AS RATE_SUM_AMT_LM     -- 上月存款发生额利率合计
                        ,CASE WHEN DEP.DATA_DATE='#V_PREV_YEAR_DATE#'  THEN COALESCE(DEP.RATE,0)          ELSE 0   END       AS RATE_SUM_AMT_LY     -- 去年同期存款发生额利率合计
                        ,CASE WHEN DEP.DATA_DATE='#V_DATA_DATE#'       THEN 1                                      END       AS AMT_COUNT           -- 当月存款发生额笔数
                        ,CASE WHEN DEP.DATA_DATE='#V_PREV_MONTH_DATE#' THEN 1                                      END       AS AMT_COUNT_LM        -- 上月存款发生额笔数
                        ,CASE WHEN DEP.DATA_DATE='#V_PREV_YEAR_DATE#'  THEN 1                                      END       AS AMT_COUNT_LY        -- 去年同期存款发生额笔数
                FROM EDW.DS_DEPB_SUM  DEP
                -- 与机构表最细级关联
                LEFT JOIN DIMENSION.T_ORG_BIZ_LVL ORG ON DEP.FIN_ORG_NO=ORG.ORG_CODE_4 AND ORG.IS_ACTIVE= '1' AND '#V_DATA_DATE#' BETWEEN ORG.START_DATE AND ORG.END_DATE
                -- 与地区表最细级关联
                LEFT JOIN (SELECT * FROM DIMENSION.T_ORG_AREA_RELATION WHERE AREA_LEVEL='3') REAL ON DEP.FIN_ORG_NO=REAL.ORG_ID AND '#V_DATA_DATE#' BETWEEN REAL.START_DATE AND REAL.END_DATE
                INNER JOIN DIMENSION.T_AREA_CODE AREA ON REAL.AREA_CODE=AREA.AREA_CODE_4 AND '#V_DATA_DATE#' BETWEEN AREA.START_DATE AND AREA.END_DATE
                -- 存款产品类别
                LEFT JOIN DIMENSION.DIM_DEPOSIT_PRODUCT PRO  ON DEP.DEPOSIT_TYPE=PRO.DEPT_PRODUCT_ID AND PRO.IS_VALID= '1' AND '#V_DATA_DATE#' BETWEEN PRO.START_DATE AND PRO.END_DATE
                WHERE DEP.DATA_DATE IN ('#V_DATA_DATE#' ,'#V_PREV_MONTH_DATE#','#V_PREV_YEAR_DATE#') -- 本月末,上月末,去年同期
        )T
        GROUP BY   T.ORG_CODE_1
                  ,T.ORG_DSCR_1
                  ,T.ORG_CODE_2
                  ,T.ORG_DSCR_2
                  ,T.ORG_CODE_3
                  ,T.ORG_DSCR_3
                  ,T.AREA_CODE_1
                  ,T.AREA_DSCR_1
                  ,T.AREA_CODE_2
                  ,T.AREA_DSCR_2
                  ,T.AREA_CODE_3
                  ,T.AREA_DSCR_3
                  ,T.CUSTOMER_TYPE
                  ,T.PRODUCT_CATEGORY
                  ,T.DEP_PERIOD
                  ,T.CCY
                  ;